import datetime
import json
from decimal import *
import xlwt

from Db_interface import DB_interface



class GetXhhgAnnualAeeInfo():
    def __init__(self):
        self.kf_con = DB_interface().exploit_db()  # 开发数据库
        self.online_con = DB_interface().online_db()  # 线上数据库
        self.kf_cur = self.kf_con.cursor()
        self.online_cur = self.online_con.cursor()
        self.bd_list = []
        self.nf_list = []

    def Get_fee_info(self):
        sql = """SELECT B.application_number '申请号',
        B.number '年份', B.late_fee '滞纳金',
        B.stamp_duty_fee '印花税',
        B.restore_fee '恢复费',
        B.annual_fee '年费',
        A.money_details '缴费详情',
        A.pay_time '缴费日期',
        A.actual_money '缴费金额',
        B.payment_deadline_date '年费截止日',
        B.late_fee_deadline_date '滞纳金截止日',
        B.is_first '是否为办登'
         FROM (SELECT * FROM pay_records WHERE type='11' AND is_del='0' AND `status`='1' AND pay_country='2') A 
         LEFT JOIN pay_records_annual_fee B ON A.id=B.pay_records_id 
         WHERE B.pay_status='1'"""
        # is_first  1为办登
        self.kf_cur.execute(sql)
        returndata = self.kf_cur.fetchall()  # 获取的是客户缴纳到公司的费用信息
        return returndata

    def deal_patent_info(self, data):
        item = {}
        # print(data)
        item['sqh'] = data[0].replace("ZL", "")
        annual_num = data[1]
        znj = str(data[2].quantize(Decimal('0.0')))
        num = str(data[5].quantize(Decimal('0.0')))
        if znj != '0.0':
            if item['sqh'][4] == "1":
                item['fee_type'] = f"发明专利第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + f"元+滞纳金{znj}元"
            elif item['sqh'][4] == "2":
                item['fee_type'] = f"实用新型专利第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + f"元+滞纳金{znj}元"
            else:
                item['fee_type'] = f"外观设计第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + f"元+滞纳金{znj}元"
            item['annual_money'] = int(float(num)) + int(float(znj))
        else:
            if item['sqh'][4] == "1":
                item['fee_type'] = f"发明专利第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + "元"
            elif item['sqh'][4] == "2":
                item['fee_type'] = f"实用新型专利第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + "元"
            else:
                item['fee_type'] = f"外观设计第{annual_num}年年费"
                item['annual_type'] = item['fee_type'] + num + "元"
            item['annual_money'] = int(float(num))
        item['client_money'] = str(data[8].quantize(Decimal('0.0')))
        item['pay_time'] = datetime.datetime.strftime(data[7], "%Y-%m-%d")
        item['sxf'] = json.loads(data[6])['ptf']
        item['is_first'] = data[-1]
        return item

    def save_excel(self, info_list, now, ww):  # 办登表
        if ww == 'bd':
            excel_name = f'./save_excel/{now}办登费用信息.xls'
        else:
            excel_name = f'./save_excel/{now}年费费用信息.xls'
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN  # 对边框对象进行操作，指定边框上下左右的边框类型为虚线
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        borders.left_colour = 0x40  # 指定上下左右的边框颜色为0x40
        borders.right_colour = 0x40
        borders.top_colour = 0x40
        borders.bottom_colour = 0x40
        style = xlwt.XFStyle()  # Create Style   #创建样式对象
        style.borders = borders
        sheet.write(0, 0, '序号', style)
        sheet.write(0, 1, '申请号', style)
        sheet.write(0, 2, '专利名称', style)
        sheet.write(0, 3, xlwt.Formula('"应缴费（共"&SUM(D2:D64102)&"人民币)"'), style)
        sheet.write(0, 4, '应缴费类型', style)
        sheet.write(0, 5, '国家截止日', style)
        sheet.write(0, 6, '缴费金额', style)
        sheet.write(0, 7, '缴费日期', style)
        sheet.write(0, 8, '联系人', style)
        sheet.write(0, 9, '电话', style)
        sheet.write(0, 10, '代理费', style)
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, i + 1, style)
                sheet.write(i + 1, 1, info_list[i]['sqh'], style)
                sheet.write(i + 1, 2, info_list[i]['name'], style)
                sheet.write(i + 1, 3, info_list[i]['annual_money'], style)
                sheet.write(i + 1, 4, info_list[i]['annual_type'], style)
                sheet.write(i + 1, 5, info_list[i]['jzr'], style)
                sheet.write(i + 1, 6, info_list[i]['client_money'], style)
                sheet.write(i + 1, 7, info_list[i]['pay_time'], style)
                sheet.write(i + 1, 8, info_list[i]['lxr'], style)
                sheet.write(i + 1, 9, info_list[i]['lxr_tel'], style)
                sheet.write(i + 1, 10, info_list[i]['sxf'], style)
            workbook.save(excel_name)

    def get_jzr(self, item):
        sql = f"SELECT money, close_date FROM annual_fee_information WHERE `application_number` = '{item['sqh']}' AND `money_type` = '{item['fee_type']}'"
        self.online_cur.execute(sql)
        data = self.online_cur.fetchall()
        try:
            item['jzr'] = data[0][1]
        except:
            sql_1 = f"SELECT id FROM `big_data`.`by_payment_information` WHERE `application_number` = '{item['sqh']}' AND `cost_category` = '{item['fee_type']}' ORDER BY `id`"
            self.online_cur.execute(sql_1)
            data_1 = self.online_cur.fetchall()
            if data_1:
                item['jzr'] = "该案件已经缴费！"
            else:
                item["jzr"] = '未查询到该案件截止日！'
        return item

    def get_patent_info(self, item):
        sql = f"SELECT `name`, organization, submit_at, unique_number FROM `mykhadmin`.`finals` WHERE `application_number` = 'ZL{item['sqh']}'"
        self.kf_cur.execute(sql)
        data = self.kf_cur.fetchall()

        try:
            item['name'] = data[0][0]
            item['dljg'] = data[0][1]
            item['sqr'] = datetime.datetime.strftime(data[0][2], "%Y-%m-%d")
        except:
            item['name'] = ""
            item['dljg'] = ""
            item['sqr'] = ""
        try:
            item['order_number'] = data[0][3]
        except:
            item['order_number'] = ''
        if item['order_number']:
            sql_sel = f"SELECT liaisons, liaisons_tel FROM `mykhadmin`.`orders` WHERE `order_number` = '{item['order_number']}'"
            self.kf_cur.execute(sql_sel)
            data_1 = self.kf_cur.fetchall()
            item['lxr'] = data_1[0][0]
            item['lxr_tel'] = data_1[0][1]
        return item

    def get_ccy_piad(self, item):
        sql = f"SELECT id FROM `big_data`.`ccy_paid` WHERE `application_number` = '{item['sqh']}' AND `money_type` = '{item['fee_type']}'"
        self.online_cur.execute(sql)
        data = self.online_cur.fetchall()
        return item, data

    def get_sys_db_st_info(self, now):
        """处理系统中受通费用信息"""
        sql = """SELECT B.application_number, A.pay_time, B.unique_number, B.name, B.submit_at, A.money, A.actual_money, A.platformMoney, A.discount_money, B.order_number from (SELECT
                    finals_id, pay_time,money,actual_money,platformMoney, discount_money
                FROM
                    `mykhadmin`.`pay_records` 
                WHERE
                    `is_del` = '0' 
                    AND `pay_country` = '2'
                    AND `finals_id` IS NOT NULL
                    AND (`type` = '1' or `type` = '2') and status = '1'
                GROUP BY finals_id)A LEFT JOIN finals B ON B.id = A.finals_id WHERE B.application_number IS NOT NULL AND B.is_stop = '0'"""
        self.kf_cur.execute(sql)
        returndata = self.kf_cur.fetchall()  # 获取到的是开发库中所有已交申请费的案子申请号跟对应的缴费日期
        for i in returndata:
            item = {}
            item['sqh'] = i[0].replace("ZL", "")
            item['pay_time'] = i[1].strftime("%Y-%m-%d")
            item['order_number'] = i[-1]
            item['name'] = i[3]
            item['apply_time'] = i[4].strftime("%Y-%m-%d")
            item['total_money'] = int(i[5])
            item['pay_money'] = int(i[6])
            item['ptf'] = int(i[7])
            item['yhje'] = int(i[8])
            item = self.get_fee_type(item)

    def get_fee_type(self, item):
        sql = f"SELECT money_type, money  FROM `big_data`.`annual_fee_information` WHERE `application_number` = '{item['sqh']}'"
        self.online_cur.execute(sql)
        data = self.online_cur.fetchall()
        if len(data) >= 3:
            print(data)
            item['money_type'] = data[0][0] + "+" + data[1][0] + "+" + data[2][0]
            item['money'] = int(float(data[0][1])) + int(float(data[1][1])) + int(float(data[2][1]))
        elif len(data) == 1:
            item['money_type'] = data[0][0]
            item['money'] = int(float(data[0][1]))
        else:
            item['money_type'] = "未查询到费用信息"
            item['money'] = ""
        return item

    def get_sqh(self):
        with open('sqh.txt', 'r', encoding='utf-8') as f:
            content = f.read()
            f.close()
        return content.split('\n')

    def get_content(self, item):
        """
        获取系统缴费状态
        :param sqh:
        :return:
        """
        sql = f"SELECT B.application_number, A.`status`, A.pay_time FROM `mykhadmin`.`pay_records` A LEFT JOIN finals B on B.id = A.finals_id where B.application_number = 'ZL{item['sqh']}'"
        self.kf_cur.execute(sql)
        returndata = self.kf_cur.fetchall()
        try:
            item['state'] = "已缴费" if returndata[0][1] == 1 else "未缴费先开票" if returndata[0][1] == 2 else "未缴费"
            item['pay_time'] = returndata[0][2].strftime("%Y-%m-%d")
        except:
            item['state'] = ""
            item['pay_time'] = ""
        return item

    def get_kp_info(self, item):
        """获取开票信息"""
        sql1 = f"""SELECT
                        A.`status` 
                    FROM
                        `mykhadmin`.`pay_invoice_confirm` A
                        LEFT JOIN pay_records B ON B.id = A.pay_record_id
                        LEFT JOIN orders C ON B.order_id = C.id
                        LEFT JOIN finals D ON C.order_number = D.unique_number 
                    WHERE
                        D.application_number = 'ZL{item['sqh']}'"""
        self.kf_cur.execute(sql1)
        content = self.kf_cur.fetchall()
        try:
            item['kaipiao'] = "是" if content[0][0] else ""
        except:
            item['kaipiao'] = ""
        return item

    def get_xt_mark(self, item):
        sql = f"SELECT GROUP_CONCAT(a.remark ORDER BY a.id DESC SEPARATOR '&') AS marks FROM order_remark a LEFT JOIN finals B ON a.finals_id=B.id WHERE B.`application_number`='ZL{item['sqh']}'"
        self.kf_cur.execute(sql)
        content = self.kf_cur.fetchall()
        try:
            item['marks'] = content[0][0]
        except:
            item['marks'] = ""
        return item

    def get_sys_st_fee(self, now):
        """
        处理受通
        :return:
        """
        sqh_li = self.get_sqh()
        info_list = []
        for sqh in sqh_li:
            item = {}
            item['sqh'] = sqh
            item = self.get_content(item)  # 获取缴费状态
            item = self.get_kp_info(item)  # 获取开票状态
            item = self.get_xt_mark(item)  # 获取系统备注
            item = self.get_fee_type(item)
            print(item)
            info_list.append(item)
        self.save_st_table(info_list, now)

    def save_st_table(self, info_list, now):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '序号')
        sheet.write(0, 1, '申请号')
        sheet.write(0, 2, '费用类型')
        sheet.write(0, 3, '费用金额')
        sheet.write(0, 4, '是否缴费')
        sheet.write(0, 5, '缴费时间')
        sheet.write(0, 6, '是否未缴费先开票')
        sheet.write(0, 7, '备注')
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, i + 1)
                sheet.write(i + 1, 1, info_list[i]['sqh'])
                sheet.write(i + 1, 2, info_list[i]['money_type'])
                sheet.write(i + 1, 3, info_list[i]['money'])
                sheet.write(i + 1, 4, info_list[i]['state'])
                sheet.write(i + 1, 5, info_list[i]['pay_time'])
                sheet.write(i + 1, 6, info_list[i]['kaipiao'])
                sheet.write(i + 1, 7, info_list[i]['marks'])
            workbook.save(f'./save_excel/{now}受通已缴费.xls')

    def main_fun(self):
        now = datetime.datetime.now().strftime("%Y-%m-%d")
        returndata = self.Get_fee_info()  # 获取系统客户已缴费公司未缴费数据-------》获取此类型的案件申请号列表
        for i in returndata:
            item = self.deal_patent_info(i)
            item = self.get_jzr(item)  # 获取截止日,并判断该案件是否已经缴费
            item, data = self.get_ccy_piad(item)  # 判断公司是否已经缴过费
            if not data:
                item = self.get_patent_info(item)  # 获取专利信息
                print(item)
                if item['is_first'] == 1:
                    self.bd_list.append(item)
                else:
                    self.nf_list.append(item)
            else:
                print("该案件公司已经交过费")
            print("_-" * 50)
        print("***************************** 开始处理办登信息！ *****************************")
        self.save_excel(self.bd_list, now, 'bd')  # 写入Excel表格
        print('***************************** 开始处理年费信息！ *****************************')
        self.save_excel(self.nf_list, now, 'nf')
        print('***************************** 开始处理受通费用！ *****************************')
        # self.get_sys_db_st_info(now)        # 该方法查询系统中的应交申请费案件
        self.get_sys_st_fee(now)
        self.kf_cur.close()
        self.kf_con.close()
        self.online_cur.close()
        self.online_con.close()


if __name__ == '__main__':
    now = datetime.datetime.now().strftime("%Y-%m-%d")
    GetXhhgAnnualAeeInfo().main_fun()
